Amazon RDS for MySQL と全文検索

藤本真司

2017.07.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、藤本です。

先日開催された Developers.IO 2017 で「Amazon Elasticsearch Service の使いドコロ」というタイトルで登壇しました。

Developers.IO 2017セッション「Amazon Elasticsearch Service の使いドコロ」で話しました #cmdevio2017

資料を作成する中で MySQL 5.7 から追加された全文検索の日本語対応に関して調べました。せっかくなのでまとめた内容をブログに書き出すとともに、RDS だとどこまでできるのかということを追加調査してみました。

MySQL 5.7 の日本語全文検索に関しては公式ドキュメントや、Oracle の方のスライドに詳しく説明されていますので、詳しく知りたい方は下記をご参照ください。

ちなみに今回お話するのは MySQL の全文検索機能で Mroonga などのプラグインには触れません。

MySQL と全文検索

MySQL は古くから全文検索機能が実装されていました。そして MySQL 5.7 から InnoDB にて全文検索機能が日本語対応しました。それにより日本語を取り扱うシステムでも利用できるようになりました。

MySQL 4.0 : MyISAM に全文検索機能
MySQL 5.6 : InnoDB に全文検索機能
MySQL 5.7 : InnoDB の全文検索が日本語対応

日本語対応

MySQL 5.7 から日本語対応できるようになったのは、パーサー（Elasticsearch で言う Tokenizer）に NGram、Mecab が追加されたことによります。

Mecab パーサー辞書を使って意味のある単語で Term に分解する。辞書を使って Term に分解するため、検索ワードに対して、不適切なレコードが含まれる可能性が低い。ただし、辞書に登録されている単語に依存するため、適切なレコードが検索結果に含まれない可能性がある。特に新語や、砕けた日本語は適切な Term に分解されない可能性がある。他にも利用者はパーサーを意識しないため部分一致を狙って検索ワードを指定するようであれば、ヒットしない可能性がある。
NGram パーサー指定した文字数の Term に分解する。文字数は 2 〜 4文字といったレンジで指定できます。下記例では 2文字に指定。 Term が辞書に依存しないため、適切なレコードが検索結果に含まれる可能性が高い。ただし、Term が意味をなさないため、不適切なレコードが検索結果に含まれる可能性が高い。

検索モード

検索する時にいくつかの検索モードが存在します。

Natural Language
パーサーを通して OR 検索
Boolean（大きく 2通り）
パーサーを通してフレーズ検索（AND かつ、順番指定）
パーサーを通さない Term レベルの検索（AND/OR/NOT を組み合わせ可）
Query Expansion
Natural Language の拡張。Natural Language での検索結果から特徴的な Term を抽出して、検索クエリに自動で加える。

例えば、検索ワード「クラスメソッド」で検索すると「クラス」「メソッド」の 2つの Term に分割されて検索されます。

Natural Language モードの場合（MATCH (column) AGAINST ('クラスメソッド' IN NATURAL LANGUAGE MODE)）、「クラス」「メソッド」のいずれかを含むレコードが返ってきます。
Boolean モードの場合（MATCH (column) AGAINST ('クラスメソッド' IN BOOLEAN MODE)）、「クラス」「メソッド」のどちらも含む、かつ「メソッド」は「クラス」の次に出て来るレコードが返ってきます。
単純に「クラス」「メソッド」の両方を含んでいるレコードを取得したい場合は Term レベルの検索を行います（MATCH (column) AGAINST ('+クラス +メソッド' IN BOOLEAN MODE)）

なので、、、「クラスメソッド」というレコードには 3つの検索モードともにレコードが返ります。「メソッドクラス」というレコードには 1つ目と3つ目はレコードが返りますが、2つ目は返りません。「クラス」というレコードには 1つ目はレコードが返りますが、2つ目と3つ目は返りません。

といった使い分けとなります。Query Expansion は他の検索モードと比べて特殊なので今回は解説しません。

文字列の正規化

Elasticsearch には Character Filter、Token Filter と様々な正規化手法がありますが、MySQL でできる正規化（？）はストップワードの指定のみとなります。

ストップワード

ストップワードとは指定した Term を転置インデックスに登録しない設定です。よく使われる例では助詞（「が」「は」「の」など）や助動詞（「です」「ます」など）を登録します。ストップワードを登録することで不要な Term を登録しないため、検索パフォーマンスの劣化を防いだり、不要な Term を検索しないため、不適切なレコードが検索結果に含まれる可能性が低くなったりします。

Amazon RDS for MySQL と全文検索

Amazon RDS for MySQL も MySQL 5.6/5.7 をサポートしているため、全文検索を利用することができます。一部、制限があります。

日本語対応

Amazon RDS for MySQL では日本語全文検索に NGram パーサーのみを利用できます。 Mecab パーサーは元々、標準インストールされているものではなく、プラグインをインストールする必要があるのですが、RDS for MySQL 5.7 ではインストールされていませんし、インストールすることができません。

試してみた

それでは実際に SQL を実行して動作を確認してみましょう。

RDS for MySQL を起動します。DB Engine Version は RDS for MySQL の最新バージョンの MySQL 5.7.17 を選択しています。パラメータグループをちょいちょいイジるのでデフォルトではないパラメータグループを用意して、設定します。

RDS が起動したら、mysqlコマンドで接続します。

$ mysql -h xxxxxxx.ap-northeast-1.rds.amazonaws.com -uuser -p
Enter password:

mysql> use db
Database changed

全文検索のテーブル定義

全文検索のインデックスを定義する場合、FULLTEXT句を利用します。

mysql> CREATE TABLE blog (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    content TEXT,
    FULLTEXT (content) WITH PARSER ngram
) ENGINE=InnoDB CHARACTER SET utf8mb4;

FULLTEXT (全文検索するカラム名) WITH PARSER 利用するPARSER

という構文です。全文検索するカラム名は単一のカラムだけでなく、複数のカラム名も指定することが可能です。

既存のテーブルに対してインデックスを追加することもできます。既存のテーブルに既に大容量のデータが入っているとインデックス処理による負荷増や、ディスク使用量増にご注意ください。

mysql> ALTER TABLE blog
    ADD
    FULLTEXT (content) WITH PARSER ngram;

転置インデックス生成

通常のインデックス同様、レコードが作成されると、転置インデックスが生成されます。

mysql> INSERT INTO blog (content) VALUES ('ブログです'), ('クラスメソッドです');

ちなみにどのような転置インデックスが生成されているか確認できます。それについては後ほど触れます。

検索

検索します。

転置インデックスから全文検索する場合、MATCH AGAINST句を利用します。

mysql> SELECT * FROM blog
    WHERE 
    MATCH (content) AGAINST ('ブログ' IN NATURAL LANGUAGE MODE);
+----+-----------------+
| id | content         |
+----+-----------------+
|  1 | ブログです      |
+----+-----------------+
1 row in set (0.01 sec)

全文検索なのか分かりづらい結果ですが。。ブログが含まれるレコードが返ってきました。

MATCH (全文検索するカラム名) AGAINST ('検索ワード' IN 検索モード)

という構文です。カラム名はテーブル定義したものと同じ名前を指定する必要があります。検索モードは先に紹介した 3つの検索モードのいづれかを指定します。指定しない場合は Natural Language モードで動作します。

スコアリング

全文検索の特徴として検索ワードがどの程度マッチしているかを表すスコアを返すことができます。スコアによってソートして返すことで検索者は一番マッチしているレコードを最初に目にすることができます。MySQL では MATCH AGAINST句 でスコアを返しています。SELECT 対象に MATCH AGAINST句 を含めることでスコアを確認することができます。

mysql> SELECT content,
    MATCH (content) AGAINST ('ブログ' IN NATURAL LANGUAGE MODE) AS score
    FROM blog;
+-----------------------------+--------------------+
| content                     | score              |
+-----------------------------+--------------------+
| ブログです                  | 0.1812381148338318 |
| クラスメソッドです          |                  0 |
+-----------------------------+--------------------+
2 rows in set (0.02 sec)

転置インデックス確認

どのような Term が転置インデックスに含まれているのか INFORMATION_SCHEMA の INNODB_FT_INDEX_CACHE から確認可能です。

転置インデックスを確認する場合、どのテーブルを確認するか、事前にパラメータグループから指定する必要があります。

パラメータ名innodb_ft_aux_table に db_name/table_name のフォーマットで指定します。今回であれば、db/blog という指定になります。

mysql> show variables like 'innodb_ft_aux_table';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| innodb_ft_aux_table | db/blog |
+---------------------+---------+
1 row in set (0.01 sec)

それでは転置インデックスの情報を確認してみましょう。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE WHERE DOC_ID=2;
+--------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| です | 2 | 3 | 2 | 2 | 9 |
| グで | 2 | 2 | 1 | 2 | 6 |
| ブロ | 2 | 2 | 1 | 2 | 0 |
| ログ | 2 | 2 | 1 | 2 | 3 |
+--------+--------------+-------------+-----------+--------+----------+
4 rows in set (0.01 sec)

WORD が文字列から分解された Term となります。今回は NGram の 2文字なので、「ブログです」は「ブロ」「ログ」「グで」「です」の 4つの Term が登録されています。

転置インデックスは単純な Term とマッピングする ID だけでなく、含まれる DOC_ID の件数、ID の範囲、含まれている位置という情報を保持しています。これにより、検索パフォーマンス向上や、フレーズ検索を実現しています。

ストップワード

続いて、ストップワードを設定します。デフォルトでもストップワードは登録されているのですが、英単語のみです。日本語だったり、任意だったりのストップワードを登録したい場合は、個別にストップワードを管理するテーブルを作成します。

テーブル名は任意のもので問題ありません。

mysql> CREATE TABLE my_stopwords(
value VARCHAR(30)) ENGINE=InnoDB CHARACTER SET utf8mb4;

ストップワードを登録していきます。INSERT 句でレコードを追加するだけです。今回は「です」をストップワードとして登録します。

mysql> insert into my_stopwords values ('です');
Query OK, 1 row affected (0.01 sec)

ストップワードを有効化する場合、有効化するか否か、どのテーブルでストップワードを管理しているか、パラメータグループから指定する必要があります。

パラメータ名innodb_ft_enable_stopword に 1 を、パラメータ名innodb_ft_server_stopword_table に db_name/table_name のフォーマットで指定します。今回であれば、db/my_stopwords という指定方法となります。

mysql> show variables like 'innodb_ft_%_stopword%';
+---------------------------------+-----------------+
| Variable_name | Value |
+---------------------------------+-----------------+
| innodb_ft_enable_stopword | ON |
| innodb_ft_server_stopword_table | db/my_stopwords |
| innodb_ft_user_stopword_table | |
+---------------------------------+-----------------+
3 rows in set (0.01 sec)

分かりやすく、一度インデックスをリセットしたいので、テーブルを削除・再作成し、データも投入し直します。INFORMATION_SCHEMA から転置インデックスを再度確認します。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| グで | 2 | 2 | 1 | 2 | 6 |
| ブロ | 2 | 2 | 1 | 2 | 0 |
| ログ | 2 | 2 | 1 | 2 | 3 |
+--------+--------------+-------------+-----------+--------+----------+
3 rows in set (0.01 sec)

「です」が転置インデックスから除外されました。ただし、NGram ではパーサーの特性からストップワードの効力は小さいです。